﻿//
// EbayTransactionDAL.cs
//  This file is responsible for reading/updating transaction (i.e., order) information from/to database.
//  Author: Zhi Wang
//  Date: 2012-12-19
//

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;

namespace EbayMaster
{
    public class EbayTransactionDAL
    {
        // Get all orders.
        //  Returns the DataTable contains all transactions.
        public static DataTable GetAllOrders()
        {
            String sql_getAllTransactions = "select * from [Transaction]  order by SaleDate desc";
            DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
            return dt;
        }

        // Get all order between a time period.
        //  Returns the DataTable contains all transactions in that time period.
        public static DataTable GetAllOrdersBetween(DateTime startDate, DateTime endDate)
        {
            String sql_getAllTransactions = "";
            if (DBConnectionUtil.DBConn.dbType == DatabaseType.Access)
            {
                sql_getAllTransactions = string.Format("select * from [Transaction]  where SaleDate > #{0}# and SaleDate < #{1}# order by SaleDate desc", startDate.ToShortDateString(), endDate.ToShortDateString());
            }
            else
            {
                sql_getAllTransactions = string.Format("select * from [Transaction]  where SaleDate > '{0}' and SaleDate < '{1}' order by SaleDate desc", startDate.ToShortDateString(), endDate.ToShortDateString());
            }

            DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
            return dt;
        }

        public static DataTable GetAllOrdersPendingBuyerFeedback()
        {
            String sql_getAllTransactions
                = "select * from [Transaction]  where IsBuyerLeftFeedback=false and IsReceived=false order by SaleDate desc";
            DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
            return dt;
        }

        // Get all orders count.
        //  Returns the order count.
        public static int GetOrdersCount()
        {
            int ordersCnt = 0;
            String sql = "select count(*) from [Transaction]";
            DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);

            try
            {
                object obj = dt.Rows[0][0];
                if (obj != null)
                    Int32.TryParse(obj.ToString(), out ordersCnt);
            }
            catch (System.Exception )
            {
                
            }
            return ordersCnt;
        }

        public static int GetPendingOrdersCount()
        {
            int ordersCnt = 0;
            String sql = "select count(*) from [Transaction] where IsBuyerLeftFeedback=0";
            DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);

            try
            {
                object obj = dt.Rows[0][0];
                if (obj != null)
                    Int32.TryParse(obj.ToString(), out ordersCnt);
            }
            catch (System.Exception)
            {

            }
            return ordersCnt;
        }

        public static DataTable GetPagedPendingOrders(int pageNum, int pageSize)
        {
            // Note to use "true/false" instead of "1/0" for MS Access.
            String pagedOrderFormatSql = "select * from [Transaction] where TransactionId "
                    + "in (select top {0} sub.TransactionId from ("
                    + " select top {1} TransactionId,SaleDate from [Transaction] where (Transaction.IsBuyerLeftFeedback=false and Transaction.IsPaid=true) order by SaleDate desc"
                    + ") [sub] order by sub.SaleDate) order by SaleDate desc";
            String pagedOrderSql = String.Format(pagedOrderFormatSql, pageSize, pageNum * pageSize);
            DataTable dt = DataFactory.ExecuteSqlReturnTable(pagedOrderSql);
            return dt;
        }

        public static DataTable GetPagedOrders(int pageNum, int pageSize)
        {
            //var sb = new StringBuilder();
            //sb.Append("select {0} from {1}");
            //sb.Append(" where {3} in (");
            //sb.Append("select top {4} sub.{3}");
            //sb.Append("    from (");
            //sb.Append("          select top {5} tab.{3}");
            //sb.Append("          from {1} as tab");
            //sb.Append("          where {2}");
            //sb.Append("          order by tab.{3}");
            //sb.Append("    ) [sub]");
            //sb.Append("    order by sub.{3} desc");
            //sb.Append(")");
            //sb.Append("order by {3}");

            //String columnsToSelect = "*";
            //String tableName = "[Transaction]";
            //String whereClause = "1=1";
            //String orderBy = "SaleDate";
            //String sql = string.Format(sb.ToString(), columnsToSelect, tableName,
            //    whereClause, orderBy, pageSize, pageNum * pageSize);

            String pagedOrderFormatSql = "select * from [Transaction] where TransactionId "
                    + "in (select top {0} sub.TransactionId from ("
                    + " select top {1} TransactionId,SaleDate from [Transaction] order by SaleDate desc"
                    + ") [sub] order by sub.SaleDate) order by SaleDate desc";
            String pagedOrderSql = String.Format(pagedOrderFormatSql, pageSize, pageNum * pageSize);
            DataTable dt = DataFactory.ExecuteSqlReturnTable(pagedOrderSql);
            return dt;
        }

       public static DataTable GetAllPaidButNotShippedOrders()
       {
           String sql_getAllTransactions = "select * from [Transaction]  where IsPaid=true and IsShipped=false order by SaleDate desc";
           DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
           return dt;
       }

       public static DataTable GetTransactionsByUserId(String userId)
       {
           String sql_getAllTransactions = string.Format("select * from [Transaction]  where BuyerId='{0}' order by SaleDate desc", userId);
           DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
           return dt;
       }

        public static DataTable GetPendingTransactionByUserId(String userId)
       {
           String sql_getAllTransactions = string.Format("select * from [Transaction]  where BuyerId='{0}' and IsBuyerLeftFeedback=false order by SaleDate desc", userId);
           DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
           return dt;
       }

       public static List<EbayTransactionType> GetOrderTransactions(String orderId)
       {
           String sql_getOneTransaction = "select * from [Transaction] where OrderId='" + orderId + "'";
           DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getOneTransaction);
           if (dt.Rows.Count == 0)
               return null;

           List<EbayTransactionType> trans = new List<EbayTransactionType>();
           foreach (DataRow row in dt.Rows)
           {
               EbayTransactionType tran = new EbayTransactionType();
               tran.OrderId = orderId;
               tran.EbayTransactionId = StringUtil.GetSafeString(row["EbayTransactionId"]);
               // ZHI_TODO:

               trans.Add(tran);
           }

           return trans;
       }

       public static bool UpdateTransactionsShippingService(List<string> orderIds, string shippingServiceCode, string shippingServiceDesc)
       {
           // if transIds = (1001, 1002, 1003), then
           // transIdsStr = 1001, 1002, 1003
           String orderIdsStr = "";
           bool first = true;
           foreach (string orderId in orderIds)
           {
               if (first == false)
                   orderIdsStr += ",";
               orderIdsStr += "'" + orderId.ToString() + "'";
               if (first == true)
                   first = false;
           }

           String sql = "update [Transaction] set ShippingServiceCode='" + shippingServiceCode + "', ShippingService='"
               + shippingServiceDesc + "' where OrderId in (" + orderIdsStr + ")";

           DataFactory.ExecuteSql(sql);
           return true;
       }


       public static bool updateTransactionShippingCost(int transId, double shippingCost)
       {
           String sql = string.Format("update [Transaction] set ShippingCost={0} where TransactionId={1}",
               shippingCost, transId);
           DataFactory.ExecuteSql(sql);
           return true;
       }

       public static bool UpdateTransactionShippedStatus(int transId, bool shipped, DateTime shippedDate)
       {
           String sql = string.Format("update [Transaction] set IsShipped={0}, ShippedDate='{1}' where TransactionId={2}",
               shipped ? 1 : 0, GetSensibleDateTime(shippedDate), transId);
           DataFactory.ExecuteSql(sql);
           return true;
       }

       public static bool UpdateTransactionSellerLeftFeedback(int transId, bool bSellerLeftFeedback)
       {
           String sql = string.Format("update [Transaction] set IsSellerLeftFeedback={0} where TransactionId={1}",
               bSellerLeftFeedback ? 1 : 0, transId);
           DataFactory.ExecuteSql(sql);
           return true;
       }

       public static bool UpdateTransactionBuyerLeftFeedback(int transId, bool bBuyerLeftFeedback)
       {
           String sql = string.Format("update [Transaction] set IsBuyerLeftFeedback={0} where TransactionId={1}",
               bBuyerLeftFeedback ? 1 : 0, transId);
           DataFactory.ExecuteSql(sql);
           return true;
       }

       public static bool UpdateTransactionItemSKU(int transId, string SKU)
       {
           String sql = "update [Transaction] set ItemSKU='" + SKU + "' where TransactionId=" + transId;
           DataFactory.ExecuteSql(sql);
           return true;
       }

       public static EbayTransactionType GetOneTransaction(String orderId, String transId)
       {
           String sql_getOneTransaction = string.Format("select * from [Transaction] where OrderId='{0}' and EbayTransactionId='{1}'", orderId, transId);
           DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getOneTransaction);
          if (dt.Rows.Count == 0)
             return null;

          DataRow dr = dt.Rows[0];
          EbayTransactionType trans = new EbayTransactionType();
          trans.TransactionId = StringUtil.GetSafeInt(dr["TransactionId"]);
          trans.SellerName = StringUtil.GetSafeString(dr["SellerName"]);
          trans.OrderId = StringUtil.GetSafeString(dr["OrderId"]);
          trans.EbayTransactionId = StringUtil.GetSafeString(dr["EbayTransactionId"]);
          trans.EbayRecordId = StringUtil.GetSafeInt(dr["EbayRecordId"]);

          trans.BuyerId = StringUtil.GetSafeString(dr["BuyerId"]);
          trans.BuyerRating = StringUtil.GetSafeInt(dr["BuyerRating"]);
          trans.BuyerCountryEbayCode = StringUtil.GetSafeString(dr["BuyerCountryEbayCode"]);
          trans.BuyerCountry4PXCode = StringUtil.GetSafeString(dr["BuyerCountry4PXCode"]);
          trans.BuyerCountry = StringUtil.GetSafeString(dr["BuyerCountry"]);
          trans.BuyerCompanyName = StringUtil.GetSafeString(dr["BuyerCompanyName"]);
          trans.BuyerName = StringUtil.GetSafeString(dr["BuyerName"]);
          trans.BuyerStateOrProvince = StringUtil.GetSafeString(dr["BuyerStateOrProvince"]);
          trans.BuyerCity = StringUtil.GetSafeString(dr["BuyerCity"]);
          trans.BuyerTel = StringUtil.GetSafeString(dr["BuyerTel"]);
          trans.BuyerMail = StringUtil.GetSafeString(dr["BuyerMail"]);
          trans.BuyerPostalCode = StringUtil.GetSafeString(dr["BuyerPostalCode"]);
          trans.BuyerAddress = StringUtil.GetSafeString(dr["BuyerAddress"]);
          trans.BuyerAddressCompact = StringUtil.GetSafeString(dr["BuyerAddressCompact"]);
          trans.BuyerAddressLine1 = StringUtil.GetSafeString(dr["BuyerAddressLine1"]);
          trans.BuyerAddressLine2 = StringUtil.GetSafeString(dr["BuyerAddressLine2"]);
          trans.BuyerPayPal = StringUtil.GetSafeString(dr["BuyerPayPal"]);

          trans.ItemId = StringUtil.GetSafeString(dr["ItemId"]);
          trans.ItemTitle = StringUtil.GetSafeString(dr["ItemTitle"]);
          trans.ItemSKU = StringUtil.GetSafeString(dr["ItemSKU"]);
          trans.ItemPrice = StringUtil.GetSafeDouble(dr["ItemPrice"]);
          trans.SaleQuantity = StringUtil.GetSafeInt(dr["SaleQuantity"]);
          trans.TotalPrice = StringUtil.GetSafeDouble(dr["TotalPrice"]);
          trans.CurrencyId = StringUtil.GetSafeString(dr["CurrencyId"]);

          trans.SaleDate = StringUtil.GetSafeDateTime(dr["SaleDate"]);
          trans.SaleDateCN = StringUtil.GetSafeDateTime(dr["SaleDateCN"]);
          trans.IsPaid = StringUtil.GetSafeBool(dr["IsPaid"]);
          trans.PaidDate = StringUtil.GetSafeDateTime(dr["PaidDate"]);
          trans.IsShipped = StringUtil.GetSafeBool(dr["IsShipped"]);
          trans.ShippedDate = StringUtil.GetSafeDateTime(dr["ShippedDate"]);

          trans.ShippingServiceCode = StringUtil.GetSafeString(dr["ShippingServiceCode"]);
          trans.ShippingService = StringUtil.GetSafeString(dr["ShippingService"]);
          trans.ShippingTrackingNo = StringUtil.GetSafeString(dr["ShippingTrackingNo"]);
          trans.ShippingCost = StringUtil.GetSafeDouble(dr["ShippingCost"]);
          trans.FinalValueFee = StringUtil.GetSafeDouble(dr["FinalValueFee"]);
          trans.PayPalFee = StringUtil.GetSafeDouble(dr["PayPalFee"]);

          trans.IsReceived = StringUtil.GetSafeBool(dr["IsReceived"]);
          trans.IsBuyerLeftFeedback = StringUtil.GetSafeBool(dr["IsBuyerLeftFeedback"]);
          trans.IsSellerLeftFeedback = StringUtil.GetSafeBool(dr["IsSellerLeftFeedback"]); 
          // ZHI_TODO: fill other field
          return trans;
       }

        public static List<EbayTransactionType> GetAllTransactions()
        {
            List<EbayTransactionType> list = new List<EbayTransactionType>();

            String sql_getAllTransactions = "select * from [Transaction] order by SaleDate desc";
            DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);

            foreach (DataRow row in dt.Rows)
            {
                EbayTransactionType trans = new EbayTransactionType();
                trans.TransactionId = StringUtil.GetSafeInt(row["TransactionId"]);
                trans.SellerName = StringUtil.GetSafeString(row["SellerName"]);
                trans.OrderId = StringUtil.GetSafeString(row["OrderId"]);
                trans.EbayTransactionId = StringUtil.GetSafeString(row["EbayTransactionId"]);
                trans.EbayRecordId = StringUtil.GetSafeInt(row["EbayRecordId"]);

                trans.BuyerId = row["BuyerId"].ToString();
                trans.BuyerRating = Convert.ToInt32(row["BuyerRating"]);
                trans.BuyerCountry = row["BuyerCountry"].ToString();
                trans.BuyerAddress = row["BuyerAddress"].ToString();
                trans.BuyerAddressCompact = row["BuyerAddressCompact"].ToString();
                trans.BuyerPayPal = row["BuyerPayPal"].ToString();
                trans.ItemId = row["ItemId"].ToString();
                trans.ItemSKU = row["ItemSKU"].ToString();
                trans.ItemPrice = (double)row["ItemPrice"];
                trans.SaleQuantity = Convert.ToInt32(row["SaleQuantity"]);
                trans.SalePrice = (double)row["SalePrice"];
                trans.TotalPrice = (double)row["TotalPrice"];
                trans.CurrencyId = row["CurrencyId"].ToString();
                trans.SaleDate = DateTime.Parse(row["SaleDate"].ToString());
                trans.SaleDateCN = DateTime.Parse(row["SaleDateCN"].ToString());
                trans.IsPaid = (bool)row["IsPaid"];
                trans.PaidDate = DateTime.Parse(row["PaidDate"].ToString());
                trans.IsShipped = (bool)row["IsShipped"];
                trans.ShippedDate = DateTime.Parse(row["ShippedDate"].ToString());
                trans.ShippingService = row["ShippingService"].ToString();

                // other fields.

                list.Add(trans);
            }

            return list;
        }

        public static bool UpdateOneTransaction(EbayTransactionType trans)
        {
            if (trans.IsValid() == false)
                return false;

            EbayTransactionType transLoc = EbayTransactionDAL.GetOneTransaction(trans.OrderId, trans.EbayTransactionId);
            if (transLoc == null)
            {                
                Logger.WriteSystemLog(string.Format("No transaction with EbayTransactionId={0}", trans.EbayTransactionId));
                return false;
            }

            bool needUpdateIsPaid = false;
            if (trans.IsPaid != transLoc.IsPaid)
            {
                Logger.WriteSystemLog(string.Format("Update one transaction with id={0}, set isPaid={1}", trans.EbayTransactionId, trans.IsPaid));
                needUpdateIsPaid = true;
            }

            bool needUpdateShipped = false;
            if (trans.IsShipped != transLoc.IsShipped)
            {
                Logger.WriteSystemLog(string.Format("Update one transaction with id={0}, set IsShipped={1}", trans.EbayTransactionId, trans.IsShipped));
                needUpdateShipped = true;
            }

            bool needUpdateBuyerFeedbackLeft = false;
            if (trans.IsBuyerLeftFeedback != transLoc.IsBuyerLeftFeedback)
            {
                Logger.WriteSystemLog(string.Format("Update one transaction with id={0}, set BuyerFeedbackLeft={1}", trans.EbayTransactionId, trans.IsBuyerLeftFeedback));
                needUpdateBuyerFeedbackLeft = true;
            }

            bool needUpdateSellerFeedbackLeft = false;
            if (trans.IsSellerLeftFeedback != transLoc.IsSellerLeftFeedback)
            {
                Logger.WriteSystemLog(string.Format("Update one transaction with id={0}, set SellerFeedbackLeft={1}", trans.EbayTransactionId, trans.IsSellerLeftFeedback));
                needUpdateSellerFeedbackLeft = true;
            }

            bool needUpdateSellerName = false;
            if (trans.SellerName == "")
                needUpdateSellerName = true;

            bool needUpdateTotalPrice = false;
            if (trans.TotalPrice != transLoc.TotalPrice)
                needUpdateTotalPrice = true;

            bool needUpdateSaleRecordId = false;
            if (trans.EbayRecordId != -1 && trans.EbayRecordId != transLoc.EbayRecordId)
                needUpdateSaleRecordId = true;

            bool needUpdateSaleDateCN = false;
            if (trans.SaleDateCN != null && 0 != trans.SaleDateCN.CompareTo(transLoc.SaleDateCN))
                needUpdateSaleDateCN = true;

            bool needUpdateTrackingNumber = false;
            if (trans.ShippingTrackingNo != null && trans.ShippingTrackingNo != transLoc.ShippingTrackingNo)
                needUpdateTrackingNumber = true;

            if (!needUpdateIsPaid && !needUpdateShipped && !needUpdateBuyerFeedbackLeft &&
                !needUpdateSellerFeedbackLeft && !needUpdateSellerName && !needUpdateTotalPrice &&
                !needUpdateSaleRecordId && !needUpdateSaleDateCN && !needUpdateTrackingNumber)
                return true;

            if (transLoc.TransactionId <= 0)
            {
                Logger.WriteSystemLog("Invalid transaction id.");
                return false;
            }

            if (transLoc.ItemSKU != null && transLoc.ItemSKU !="")
                trans.ItemSKU = transLoc.ItemSKU;
            if (transLoc.ShippingService != null && transLoc.ShippingService != "")
                trans.ShippingService = transLoc.ShippingService;
            if (transLoc.ShippingServiceCode != null && transLoc.ShippingServiceCode != "")
                trans.ShippingServiceCode = transLoc.ShippingServiceCode;

            bool result = UpdateOneTransactionInternal(transLoc.TransactionId, trans);
            return result;
            // Save some data.
            //trans.ShippingService = transLoc.ShippingService;
            //trans.ShippingServiceCode = transLoc.ShippingServiceCode;
            //trans.ItemSKU = transLoc.ItemSKU;

            // Delete the record and add one.
            //String sql = string.Format("delete from  [Transaction] where OrderId='{0}' and EbayTransactionId='{1}'", trans.OrderId, trans.EbayTransactionId);
            //DataFactory.ExecuteSql(sql);

            //bool result = InsertOneTransaction(trans);
            //String sql = string.Format("update [Transaction] set IsPaid={0}, PaidDate='{1}', IsShipped={2}, ShippedDate='{3}', IsBuyerLeftFeedback={4}, IsSellerLeftFeedback={5}, SellerName='{6}' where OrderId='{7}' and EbayTransactionId='{8}'",
            //    trans.IsPaid, trans.PaidDate.ToString(), trans.IsShipped, trans.ShippedDate.ToString(), trans.IsBuyerLeftFeedback, trans.IsSellerLeftFeedback, trans.SellerName,  trans.OrderId, trans.EbayTransactionId);
            //DataFactory.ExecuteSql(sql);
            //return result;
        }

        private static bool UpdateOneTransactionInternal(int transId, EbayTransactionType trans)
        {
            bool result = false;

               IDbCommand cmd = DataFactory.CreateCommand(null);
               cmd.CommandText = @"Update [Transaction] set SellerName=@SellerName, OrderId=@OrderId, EbayTransactionId=@EbayTransactionId, EbayRecordId=@EbayRecordId, BuyerId=@BuyerId, BuyerRating=@BuyerRating,"
                       + "BuyerCountryEbayCode=@BuyerCountryEbayCode, BuyerCountry4PXCode=@BuyerCountry4PXCode,"
                       + "BuyerCountry=@BuyerCountry, BuyerCompanyName=@BuyerCompanyName, BuyerName=@BuyerName, BuyerStateOrProvince=@BuyerStateOrProvince, BuyerCity=@BuyerCity,"
                       + "BuyerTel=@BuyerTel, BuyerMail=@BuyerMail, BuyerPostalCode=@BuyerPostalCode, BuyerAddress=@BuyerAddress, BuyerAddressCompact=@BuyerAddressCompact, BuyerAddressLine1=@BuyerAddressLine1, BuyerAddressLine2=@BuyerAddressLine2, "
                       + "BuyerPayPal=@BuyerPayPal,ItemId=@ItemId, ItemTitle=@ItemTitle, ItemSKU=@ItemSKU, ItemPrice=@ItemPrice, SaleQuantity=@SaleQuantity, SalePrice=@SalePrice, TotalPrice=@TotalPrice, CurrencyId=@CurrencyId,"
                       + "SaleDate=@SaleDate, SaleDateCN=@SaleDateCN, IsPaid=@IsPaid, PaidDate=@PaidDate, IsShipped=@IsShipped, ShippedDate=@ShippedDate, ShippingServiceCode=@ShippingServiceCode, ShippingService=@ShippingService, ShippingTrackingNo=@ShippingTrackingNo, ShippingCost=@ShippingCost, FinalValueFee=@FinalValueFee, PayPalFee=@PayPalFee,"
                       + "IsReceived=@IsReceived, IsBuyerLeftFeedback=@IsBuyerLeftFeedback, IsSellerLeftFeedback=@IsSellerLeftFeedback, IsNeedAttention=@IsNeedAttention, IsContactedBuyer=@IsContactedBuyer,"
                       + "LastContactedBuyerDate=@LastContactedBuyerDate, IsResendReplacement=@IsResendReplacement, UserComment=@UserComment where TransactionId=@TransactionId";

               DataFactory.AddCommandParam(cmd, "@SellerName", DbType.String, trans.SellerName);
               DataFactory.AddCommandParam(cmd, "@OrderId", DbType.String, trans.OrderId);
               DataFactory.AddCommandParam(cmd, "@EbayTransactionId", DbType.String, trans.EbayTransactionId);
               DataFactory.AddCommandParam(cmd, "@EbayRecordId", DbType.Int32, trans.EbayRecordId);
               DataFactory.AddCommandParam(cmd, "@BuyerId", DbType.String, trans.BuyerId);
               DataFactory.AddCommandParam(cmd, "@BuyerRating", DbType.Int32, trans.BuyerRating);

               DataFactory.AddCommandParam(cmd, "@BuyerCountryEbayCode", DbType.String, trans.BuyerCountryEbayCode);
               DataFactory.AddCommandParam(cmd, "@BuyerCountry4PXCode", DbType.String, trans.BuyerCountry4PXCode);
               DataFactory.AddCommandParam(cmd, "@BuyerCountry", DbType.String, trans.BuyerCountry);
               DataFactory.AddCommandParam(cmd, "@BuyerCompanyName", DbType.String, trans.BuyerCompanyName);
               DataFactory.AddCommandParam(cmd, "@BuyerName", DbType.String, trans.BuyerName);

               DataFactory.AddCommandParam(cmd, "@BuyerStateOrProvince", DbType.String, trans.BuyerStateOrProvince);
               DataFactory.AddCommandParam(cmd, "@BuyerCity", DbType.String, trans.BuyerCity);
               DataFactory.AddCommandParam(cmd, "@BuyerTel", DbType.String, trans.BuyerTel);
               DataFactory.AddCommandParam(cmd, "@BuyerMail", DbType.String, trans.BuyerMail);
               DataFactory.AddCommandParam(cmd, "@BuyerPostalCode", DbType.String, trans.BuyerPostalCode);

               DataFactory.AddCommandParam(cmd, "@BuyerAddress", DbType.String, trans.BuyerAddress);
               DataFactory.AddCommandParam(cmd, "@BuyerAddressCompact", DbType.String, trans.BuyerAddressCompact);
               DataFactory.AddCommandParam(cmd, "@BuyerAddressLine1", DbType.String, trans.BuyerAddressLine1);
               DataFactory.AddCommandParam(cmd, "@BuyerAddressLine2", DbType.String, trans.BuyerAddressLine2);
               DataFactory.AddCommandParam(cmd, "@BuyerPayPal", DbType.String, trans.BuyerPayPal);
               DataFactory.AddCommandParam(cmd, "@ItemId", DbType.String, trans.ItemId);
               DataFactory.AddCommandParam(cmd, "@ItemTitle", DbType.String, trans.ItemTitle);

               DataFactory.AddCommandParam(cmd, "@ItemSKU", DbType.String, trans.ItemSKU);
               DataFactory.AddCommandParam(cmd, "@ItemPrice", DbType.Double, trans.ItemPrice);
               DataFactory.AddCommandParam(cmd, "@SaleQuantity", DbType.Int32, trans.SaleQuantity);
               DataFactory.AddCommandParam(cmd, "@SalePrice", DbType.Double, trans.SalePrice);
               DataFactory.AddCommandParam(cmd, "@TotalPrice", DbType.Double, trans.TotalPrice);

               DataFactory.AddCommandParam(cmd, "@CurrencyId", DbType.String, trans.CurrencyId);
               DataFactory.AddCommandParam(cmd, "@SaleDate", DbType.DateTime, trans.SaleDate.ToString());
               DataFactory.AddCommandParam(cmd, "@SaleDateCN", DbType.DateTime, GetSensibleDateTime(trans.SaleDateCN));
               DataFactory.AddCommandParam(cmd, "@IsPaid", DbType.Boolean, trans.IsPaid);
               DataFactory.AddCommandParam(cmd, "@PaidDate", DbType.DateTime, trans.PaidDate.ToString());
               DataFactory.AddCommandParam(cmd, "@IsShipped", DbType.Boolean, trans.IsShipped);

               DataFactory.AddCommandParam(cmd, "@ShippedDate", DbType.DateTime, GetSensibleDateTime(trans.ShippedDate).ToString());
               DataFactory.AddCommandParam(cmd, "@ShippingServiceCode", DbType.String, trans.ShippingServiceCode);
               DataFactory.AddCommandParam(cmd, "@ShippingService", DbType.String, trans.ShippingService);
               DataFactory.AddCommandParam(cmd, "@ShippingTrackingNo", DbType.String, trans.ShippingTrackingNo);
               DataFactory.AddCommandParam(cmd, "@ShippingCost", DbType.Double, trans.ShippingCost);
               DataFactory.AddCommandParam(cmd, "@FinalValueFee", DbType.Double, trans.FinalValueFee);
               DataFactory.AddCommandParam(cmd, "@PayPalFee", DbType.Double, trans.PayPalFee);

               DataFactory.AddCommandParam(cmd, "@IsReceived", DbType.Boolean, trans.IsReceived);
               DataFactory.AddCommandParam(cmd, "@IsBuyerLeftFeedback", DbType.Boolean, trans.IsBuyerLeftFeedback);
               DataFactory.AddCommandParam(cmd, "@IsSellerLeftFeedback", DbType.Boolean, trans.IsSellerLeftFeedback);
               DataFactory.AddCommandParam(cmd, "@IsNeedAttention", DbType.Boolean, trans.IsNeedAttention);
               DataFactory.AddCommandParam(cmd, "@IsContactedBuyer", DbType.Boolean, trans.IsContactedBuyer);

               DataFactory.AddCommandParam(cmd, "@LastContactedBuyerDate", DbType.DateTime, trans.LastContactedBuyerDate.ToString());
               DataFactory.AddCommandParam(cmd, "@IsResendReplacement", DbType.Boolean, trans.IsResendReplacement);
               DataFactory.AddCommandParam(cmd, "@UserComment", DbType.String, trans.UserComment);

               DataFactory.AddCommandParam(cmd, "@TransactionId", DbType.String, transId);

               try
               {
                   if (DataFactory.DbConnection.State == ConnectionState.Closed)
                       DataFactory.DbConnection.Open();
                   cmd.ExecuteNonQuery();
                   result = true;

               }
               catch (DataException)
               {
                   // Write to log here.
                   result = false;
               }
               finally
               {
                   if (DataFactory.DbConnection.State == ConnectionState.Open)
                       DataFactory.DbConnection.Close();
               }

               return result;
        }

        private static DateTime GetSensibleDateTime(DateTime dt)
        {
            if (dt == null || dt.Year != 2011 && dt.Year != 2012 && dt.Year != 2013)
                return DateTime.Now;

            return dt;
        }

        public static bool InsertOrUpdateOneTransaction(EbayTransactionType trans)
        {
            if (trans.IsValid() == false)
                return false;

            bool result = true;

            EbayTransactionType transLoc = EbayTransactionDAL.GetOneTransaction(trans.OrderId, trans.EbayTransactionId);
            if (transLoc != null)
            {
                result = UpdateOneTransaction(trans);
            }
            else
            {
                result = InsertOneTransaction(trans);
            }

            return true;
        }

        private static string GetDefaultValue(string raw)
        {
            if (raw == null)
                return "";
            else
                return raw;
        }

        // Private to prevent misuse, use public InsertOrUpdateOneTransaction instead!!!!!!!
        private static bool InsertOneTransaction(EbayTransactionType trans)
        {
            if (trans.IsValid() == false)
                return false;

            EbayTransactionType transLoc = EbayTransactionDAL.GetOneTransaction(trans.OrderId, trans.EbayTransactionId);
            if (transLoc != null)
            {
                Logger.WriteSystemLog(string.Format("Transaction already existed in database: userId={0}, itemTitle={1}",
                   trans.BuyerId, trans.ItemTitle));
                return false;
            }

            IDbCommand cmd = DataFactory.CreateCommand(null);
            cmd.CommandText = @"insert into [Transaction] (SellerName, OrderId, EbayTransactionId, EbayRecordId, BuyerId, BuyerRating,"
                    + "BuyerCountryEbayCode, BuyerCountry4PXCode,"
                    + "BuyerCountry, BuyerCompanyName, BuyerName, BuyerStateOrProvince, BuyerCity,"
                    + "BuyerTel, BuyerMail, BuyerPostalCode, BuyerAddress, BuyerAddressCompact, BuyerAddressLine1, BuyerAddressLine2,"
                    + "BuyerPayPal,ItemId, ItemTitle, ItemSKU, ItemPrice, SaleQuantity, SalePrice, TotalPrice, CurrencyId,"
                    + "SaleDate, SaleDateCN, IsPaid, PaidDate, IsShipped, ShippedDate, ShippingServiceCode, ShippingService, ShippingTrackingNo, ShippingCost, FinalValueFee, PayPalFee,"
                    + "IsReceived, IsBuyerLeftFeedback, IsSellerLeftFeedback, IsNeedAttention, IsContactedBuyer,"
                    + "LastContactedBuyerDate, IsResendReplacement, UserComment) values ("
                    + "@SellerName, @OrderId, @EbayTransactionId, @EbayRecordId, @BuyerId, @BuyerRating,"
                    + "@BuyerCountryEbayCode, @BuyerCountry4PXCode,"
                    + "@BuyerCountry, @BuyerCompanyName, @BuyerName, @BuyerStateOrProvince, @BuyerCity,"
                    + "@BuyerTel, @BuyerMail, @BuyerPostalCode, @BuyerAddress, @BuyerAddressCompact, @BuyerAddressLine1, @BuyerAddressLine2,"
                    + "@BuyerPayPal, @ItemId, @ItemTitle, @ItemSKU, @ItemPrice, @SaleQuantity, @SalePrice, @TotalPrice, @CurrencyId,"
                    + "@SaleDate, @SaleDateCN, @IsPaid, @PaidDate, @IsShipped, @ShippedDate, @ShippingServiceCode, @ShippingService, @ShippingTrackingNo, @ShippingCost, @FinalValueFee, @PayPalFee,"
                    + "@IsReceived, @IsBuyerLeftFeedback, @IsSellerLeftFeedback, @IsNeedAttention, @IsContactedBuyer,"
                    + "@LastContactedBuyerDate, @IsResendReplacement, @UserComment)";

            DataFactory.AddCommandParam(cmd, "@SellerName", DbType.String, trans.SellerName);
            DataFactory.AddCommandParam(cmd, "@OrderId", DbType.String, trans.OrderId);
            DataFactory.AddCommandParam(cmd, "@EbayTransactionId", DbType.String, trans.EbayTransactionId);
            DataFactory.AddCommandParam(cmd, "@EbayRecordId", DbType.Int32, trans.EbayRecordId);
            DataFactory.AddCommandParam(cmd, "@BuyerId", DbType.String, trans.BuyerId);
            DataFactory.AddCommandParam(cmd, "@BuyerRating", DbType.Int32, trans.BuyerRating);

            DataFactory.AddCommandParam(cmd, "@BuyerCountryEbayCode", DbType.String, GetDefaultValue(trans.BuyerCountryEbayCode));
            DataFactory.AddCommandParam(cmd, "@BuyerCountry4PXCode", DbType.String, GetDefaultValue(trans.BuyerCountry4PXCode));
            DataFactory.AddCommandParam(cmd, "@BuyerCountry", DbType.String, GetDefaultValue(trans.BuyerCountry));
            DataFactory.AddCommandParam(cmd, "@BuyerCompanyName", DbType.String, GetDefaultValue(trans.BuyerCompanyName));
            DataFactory.AddCommandParam(cmd, "@BuyerName", DbType.String, trans.BuyerName);

            DataFactory.AddCommandParam(cmd, "@BuyerStateOrProvince", DbType.String, trans.BuyerStateOrProvince);
            DataFactory.AddCommandParam(cmd, "@BuyerCity", DbType.String, trans.BuyerCity);
            DataFactory.AddCommandParam(cmd, "@BuyerTel", DbType.String, GetDefaultValue(trans.BuyerTel));
            DataFactory.AddCommandParam(cmd, "@BuyerMail", DbType.String, GetDefaultValue(trans.BuyerMail));
            DataFactory.AddCommandParam(cmd, "@BuyerPostalCode", DbType.String, GetDefaultValue(trans.BuyerPostalCode));

            DataFactory.AddCommandParam(cmd, "@BuyerAddress", DbType.String, GetDefaultValue(trans.BuyerAddress));
            DataFactory.AddCommandParam(cmd, "@BuyerAddressCompact", DbType.String, GetDefaultValue(trans.BuyerAddressCompact));
            DataFactory.AddCommandParam(cmd, "@BuyerAddressLine1", DbType.String, GetDefaultValue(trans.BuyerAddressLine1));
            DataFactory.AddCommandParam(cmd, "@BuyerAddressLine2", DbType.String, GetDefaultValue(trans.BuyerAddressLine2));
            DataFactory.AddCommandParam(cmd, "@BuyerPayPal", DbType.String, GetDefaultValue(trans.BuyerPayPal));
            DataFactory.AddCommandParam(cmd, "@ItemId", DbType.String, GetDefaultValue(trans.ItemId));
            DataFactory.AddCommandParam(cmd, "@ItemTitle", DbType.String, GetDefaultValue(trans.ItemTitle));

            DataFactory.AddCommandParam(cmd, "@ItemSKU", DbType.String, GetDefaultValue(trans.ItemSKU));
            DataFactory.AddCommandParam(cmd, "@ItemPrice", DbType.Double, trans.ItemPrice);
            DataFactory.AddCommandParam(cmd, "@SaleQuantity", DbType.Int32, trans.SaleQuantity);
            DataFactory.AddCommandParam(cmd, "@SalePrice", DbType.Double, trans.SalePrice);
            DataFactory.AddCommandParam(cmd, "@TotalPrice", DbType.Double, trans.TotalPrice);

            DataFactory.AddCommandParam(cmd, "@CurrencyId", DbType.String, GetDefaultValue(trans.CurrencyId));
            DataFactory.AddCommandParam(cmd, "@SaleDate", DbType.Date, GetSensibleDateTime(trans.SaleDate).ToString());
            DataFactory.AddCommandParam(cmd, "@SaleDateCN", DbType.Date, GetSensibleDateTime(trans.SaleDateCN).ToString());
            DataFactory.AddCommandParam(cmd, "@IsPaid", DbType.Boolean, trans.IsPaid);
            DataFactory.AddCommandParam(cmd, "@PaidDate", DbType.Date, GetSensibleDateTime(trans.PaidDate).ToString());
            DataFactory.AddCommandParam(cmd, "@IsShipped", DbType.Boolean, trans.IsShipped);

            DataFactory.AddCommandParam(cmd, "@ShippedDate", DbType.Date, GetSensibleDateTime(trans.ShippedDate).ToString());
            DataFactory.AddCommandParam(cmd, "@ShippingServiceCode", DbType.String, trans.ShippingServiceCode);
            DataFactory.AddCommandParam(cmd, "@ShippingService", DbType.String, trans.ShippingService);
            DataFactory.AddCommandParam(cmd, "@ShippingTrackingNo", DbType.String, trans.ShippingTrackingNo);

            DataFactory.AddCommandParam(cmd, "@ShippingCost", DbType.Double, trans.ShippingCost);
            DataFactory.AddCommandParam(cmd, "@FinalValueFee", DbType.Double, trans.FinalValueFee);
            DataFactory.AddCommandParam(cmd, "@PayPalFee", DbType.Double, trans.PayPalFee);

            DataFactory.AddCommandParam(cmd, "@IsReceived", DbType.Boolean, trans.IsReceived);
            DataFactory.AddCommandParam(cmd, "@IsBuyerLeftFeedback", DbType.Boolean, trans.IsBuyerLeftFeedback);
            DataFactory.AddCommandParam(cmd, "@IsSellerLeftFeedback", DbType.Boolean, trans.IsSellerLeftFeedback);
            DataFactory.AddCommandParam(cmd, "@IsNeedAttention", DbType.Boolean, trans.IsNeedAttention);
            DataFactory.AddCommandParam(cmd, "@IsContactedBuyer", DbType.Boolean, trans.IsContactedBuyer);

            DataFactory.AddCommandParam(cmd, "@LastContactedBuyerDate", DbType.Date, GetSensibleDateTime(trans.LastContactedBuyerDate).ToString());
            DataFactory.AddCommandParam(cmd, "@IsResendReplacement", DbType.Boolean, trans.IsResendReplacement);
            DataFactory.AddCommandParam(cmd, "@UserComment", DbType.String, GetDefaultValue(trans.UserComment));

                bool result = false;
                int newItemId = 0;

                try
                {
                    if (DataFactory.DbConnection.State == ConnectionState.Closed)
                        DataFactory.DbConnection.Open();
                    cmd.ExecuteNonQuery();

                    IDbCommand cmdNewID = DataFactory.CreateCommand("SELECT @@IDENTITY");
   
                    // Retrieve the Autonumber and store it in the CategoryID column.
                    object obj = cmdNewID.ExecuteScalar();
                    Int32.TryParse(obj.ToString(), out newItemId);
                    result = newItemId > 0;
                }
                catch (DataException ex)
                {
                    // Write to log here.
                    Logger.WriteSystemLog(string.Format("Error : {0}", ex.Message));
                    trans.dump();
                    result = false;
                }
                finally
                {
                    if (DataFactory.DbConnection.State == ConnectionState.Open)
                        DataFactory.DbConnection.Close();
                }

                return result;
        }   // InsertOneTransaction
    }
}
